﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using System.Data.Odbc;
using System.Collections.Specialized;


namespace EuroAudit
{
    public class GlobalF
    {
        /*public static class AppOptions
        {
            public static string FTPUser = "";
            public static string FTPPass = "";

        }*/
        public static NameValueCollection AppOptions = new NameValueCollection();
        
        public void InitO()
        {
            DataSet dsO = GetData("SELECT OptName, OptValue FROM options WHERE OptType='GLOBAL' AND RecStatus=0", "WebCNS", false);
            if (dsO.Tables.Count>0)
            {
                if(dsO.Tables[0].Rows.Count>0)
                {
                    foreach (DataRow drO in dsO.Tables[0].Rows)
                    {
                        GlobalF.AppOptions.Set(drO["OptName"].ToString(), drO["OptValue"].ToString());
                    }
                }
            }

        }
        
        public string EscapeSQLCahrs(string InputString)
        {
            //InputString = @InputString.Replace(@"\", @"\\");
            //InputString = @InputString.Replace(@";", @"\;");
            InputString = InputString.Replace(@"'", @"\'");
            //InputString = @InputString.Replace(@"""", @"\""");
            //InputString = @InputString.Replace("!", "");
            //InputString = @InputString.Replace("@", "");
            //InputString = @InputString.Replace("#", "");
            //InputString = @InputString.Replace("$", "");
            //InputString = @InputString.Replace("%", "%");
            //InputString = @InputString.Replace("^", "");
            //InputString = @InputString.Replace("&", "");

            return InputString;
        }
        public string GetConnectionString(string CNSName)
        {
            string s1 = "";
            string s2 = "";
            string s3 = "";
            s1 += "e";
            s2 += "E";
            s3 += "q";
            s1 += "a";
            s2 += "A";
            s3 += "w";
            s1 += "u";
            s2 += "!";
            s3 += "q";
            s1 += "s";
            s2 += "2";
            s3 += "a";
            s1 += "e";
            s2 += "i";
            s3 += "d";
            s1 += "r";
            s2 += "n";
            s3 += "t";

            // Retrieve the connection string stored in the Web.config file.
            string ProvName = ConfigurationManager.ConnectionStrings[CNSName].ProviderName;
            String connectionString = ConfigurationManager.ConnectionStrings[CNSName].ConnectionString;
            connectionString = string.Format(connectionString, s1, s2);
            return connectionString;
        }

        public DataSet GetData(String queryString, String CNStringName, bool GetLastInsertID)
        {
            string s1 = "";
            string s2 = "";
            string s3 = "";
            s1 += "e";
            s2 += "E";
            s3 += "q";
            s1 += "a";
            s2 += "A";
            s3 += "w";
            s1 += "u";
            s2 += "!";
            s3 += "q";
            s1 += "s";
            s2 += "2";
            s3 += "a";
            s1 += "e";
            s2 += "i";
            s3 += "d";
            s1 += "r";
            s2 += "n";
            s3 += "t";

            // Retrieve the connection string stored in the Web.config file.
            string ProvName = ConfigurationManager.ConnectionStrings[CNStringName].ProviderName;
            String connectionString = ConfigurationManager.ConnectionStrings[CNStringName].ConnectionString;
            connectionString = string.Format(connectionString, s1, s2);

            DataSet ds = new DataSet();

            try
            {
                // Connect to the database and run the query.
                if (ProvName == "MySql.Data.MySqlClient")
                {
                    MySqlConnection connection = new MySqlConnection(connectionString);
                    // Connect to the database and run the query.
                    //connection.ConnectionTimeout = 100;
                    connection.Open();
                    MySqlDataAdapter adapter = new MySqlDataAdapter(queryString, connection);
                    adapter.SelectCommand.CommandTimeout = 100;
                    // Fill the DataSet.
                    adapter.Fill(ds);
                    if (GetLastInsertID == true)
                    {
                        queryString = "SELECT LAST_INSERT_ID() as LID";
                        adapter = new MySqlDataAdapter(queryString, connection);
                        adapter.Fill(ds, "tLID");
                    }
                    connection.Close();
                }
                if (ProvName == "System.Data.Odbc")
                {
                    OdbcConnection connection = new OdbcConnection(connectionString);
                    // Connect to the database and run the query.
                    connection.ConnectionTimeout = 100;
                    connection.Open();
                    OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, connection);
                    adapter.SelectCommand.CommandTimeout = 100;
                    // Fill the DataSet.
                    adapter.Fill(ds);
                    if (GetLastInsertID == true)
                    {
                        queryString = "SELECT LAST_INSERT_ID() as LID";
                        adapter = new OdbcDataAdapter(queryString, connection);
                        adapter.Fill(ds, "tLID");
                    }
                    connection.Close();
                }

            }
            catch (Exception ex)
            {

                // The connection failed. Display an error message.
                //Message.Text = "Unable to connect to the database.";
                

            }

            return ds;

        }

    }


}
